package onlinebanking.server.objects;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import javax.xml.ws.RespectBinding;

import onlinebanking.network.Message;
import onlinebanking.server.SimpleServer;
import onlinebanking.sql.SQLConnection;
import onlinebanking.sql.SQLTable;

public class Transaction extends QueryObject{
	
	public static final char PENDING = 'P';
	public static final char EXPIRED = 'E';
	public static final char VALIDATED = 'V';
	public static final char DONE = 'D';
	public static final char FAILED = 'F';
	
	public static final char FUNDS_TRANSFER = 'T';
	public static final char BILL_PAYMENT = 'B';
	public static final char DEPOSIT = 'D';
	public static final char WIDTHRAWAL = 'W';
	
	//transactions table
	protected int transactionid;
	protected char transactiontypeid;
	protected char transactionstatusid;
	protected Timestamp expiration;
	protected Timestamp processedon;
	protected int initiatedby;
	protected int bankaccountid;
	



	protected boolean exists;
	 
	
	public Transaction(){
		super();
	}
	
	public Transaction(int transactionid){
		super();
		String sqlstatement = "SELECT transactionid,transactiontypeid,transstatusid,expiration,processedon,initiatedby,bankaccountid" +
				" FROM transactions WHERE transactionid = ? ;";
		//user inherited variables
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, transactionid);
		this.resultset = this.sqltable.executeSQL();
		_loadinfo();
	}
	
	private void _loadinfo(){
		try {
			//if no results, return
			exists = resultset.first();
			if(!exists)	return;	
			
			//loads variables from db
			transactionid = resultset.getInt("transactionid");
			transactiontypeid = resultset.getString("transactiontypeid").toCharArray()[0];
			transactionstatusid = resultset.getString("transstatusid").toCharArray()[0];
			expiration = resultset.getTimestamp("expiration");
			processedon = resultset.getTimestamp("processedon");
			initiatedby = resultset.getInt("initiatedby");
			bankaccountid = resultset.getInt("bankaccountid");
			
			this.checkAndUpdateIfExpired();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void setTransactionTypeID(char TransactionTypeID){
		this.transactiontypeid = TransactionTypeID;
	}
	
	public void setTransactionStatusID(char TransactionStatusID){
		this.transactionstatusid = TransactionStatusID;
	}
	
	public void setExpiration(Timestamp expiry){
		this.expiration = expiry;
	}
	
	public void setProccessedOn(Timestamp processedon){
		this.processedon = processedon;
	}
	
	public void setInitiatedBy(int personalaccountid){
		this.initiatedby = personalaccountid;
	}
	
	public void setBankAccountid(int bankaccountid){
		this.bankaccountid = bankaccountid;
	}
	
	public int getTransactionid() {
		return transactionid;
	}

	public char getTransactiontypeid() {
		return transactiontypeid;
	}

	public char getTransactionstatusid() {
		return transactionstatusid;
	}

	public Timestamp getExpiration() {
		return expiration;
	}

	public Timestamp getProcessedon() {
		return processedon;
	}

	public int getInitiatedby() {
		return initiatedby;
	}
	
	public int getBankaccountid() {
		return bankaccountid;
	}

	public boolean exists(){
		return this.exists;
	}
	
	/**
	 * @return value of the primary key inserted, -1 if unsuccessful
	 */
	public int insertToTransactionsTable(){
		
		String sqlstatement = "INSERT INTO transactions(transactiontypeid,transstatusid,expiration,initiatedby,bankaccountid) " +
				"VALUES(?,?,?,?,?);";
		
		//user inherited variables
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setCharParam(1, transactiontypeid);
		this.sqltable.setCharParam(2, transactionstatusid);
		this.sqltable.setTimeStampParam(3, expiration);	
		this.sqltable.setIntParam(4, initiatedby);
		this.sqltable.setIntParam(5, bankaccountid);
		int insertedPrimaryKey = this.sqltable.executeUpdate();
		
		
		//true when there is a change in rows
		return insertedPrimaryKey;
		
	}
	
	public int insertToFundsTransferTable(int transactionid, double ammountTransfer,
			String srcAccountno, String destAccountno,int srcbankid,int destbankid, String srcName,String destName){
		
		String sqlstatement = "INSERT INTO fundstransfers(transactionid,ammounttransfer,srcaccountno,destaccountno,srcbank,destbank,sourceName,destName) " +
		"VALUES(?,?,?,?,?,?,?,?);";
		//user inherited variables
		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setIntParam(1, transactionid);
		this.sqltable.setDoubleParam(2, ammountTransfer);
		this.sqltable.setIntParam(3, Integer.parseInt(srcAccountno));
		this.sqltable.setIntParam(4, Integer.parseInt(destAccountno));	
		this.sqltable.setIntParam(5, srcbankid);	
		this.sqltable.setIntParam(6, destbankid);	
		this.sqltable.setStringParam(7, srcName);	
		this.sqltable.setStringParam(8, destName);	
			
		int insertedPrimaryKey = this.sqltable.executeUpdate();
		return insertedPrimaryKey;
	}
	
	public int insertConfirmation(int tacid, long PAC){
		
		String sqlquerry = "SELECT transconfirmationid from transconfirmations WHERE transactionid = ? AND tacid = ?";
		this.sqltable.setSQLStatement(sqlquerry);
		this.sqltable.setIntParam(1, transactionid);
		this.sqltable.setIntParam(2, tacid);
		ResultSet rs = sqltable.executeSQL();
		//check if already confirmed
		try {
			if(rs.next()){
				return -1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		String sqlstatement = "INSERT INTO transconfirmations(transactionid,tacid,confirmedon,encryptedpac) " +
		"VALUES(?,?,?,?);";
		
		sqltable.setSQLStatement(sqlstatement);
		sqltable.setIntParam(1, transactionid);
		sqltable.setIntParam(2, tacid);
		Timestamp now = SQLTable.getNowTimeStamp();
		sqltable.setTimeStampParam(3, now);
		sqltable.setLongParam(4, PAC);
		
		int insertedPK = sqltable.executeUpdate();
		
		return insertedPK;
	}
	
	public boolean executeTransaction(){
		/*
		 * check if done or expired (HOW TO CHECK IF EXPIRED?!?!?)
		 */
		System.out.println("Transaction = HOW TO CHECK IF EXPIRED");
		if(this.transactionstatusid == Transaction.DONE || this.transactionstatusid == Transaction.EXPIRED ){
			System.out.println((this.transactionstatusid == Transaction.DONE)? "Transaction:ALREADY DONE": "Transaction: EXPIRED");
			return false;
		}
		if(this.checkAndUpdateIfExpired()){
			System.out.println("Transaction expired");
			return false;
		}
		
		
		switch (transactiontypeid) {
		case Transaction.FUNDS_TRANSFER:
			/**
			 * BETTER IF SQL FUNCTION EXISTS
			 */
			System.out.println("Transfering funds...");
			
			//get transaction details
			String sqlstatement = "SELECT ammounttransfer,srcaccountno,destaccountno,srcbank,destbank " +
					"FROM fundstransfers WHERE transactionid = ? ";
		
			this.sqltable.setSQLStatement(sqlstatement);
			this.sqltable.setIntParam(1, transactionid);
			ResultSet rs = this.sqltable.executeSQL();
			
			try {
				if(!rs.next()){
					return false;	//if no results about the transfer details
				}
				double ammounttransfer = rs.getDouble(1);
				int srcaccountno = rs.getInt(2);
				int destaccountno = rs.getInt(3);				
				BankAccount srcBA = new BankAccount(0,srcaccountno);
				BankAccount destBA = new BankAccount(0,destaccountno);
				
				
				if(!srcBA.exists() || !destBA.exists()){
					return false;
				}
				System.out.println("details validated");
				
				System.out.println("Balance:" + srcBA.getBalance() +"  Transferammount:"+ ammounttransfer);
				
				//returns true if srcBA has enough balance
				boolean allowable = (srcBA.getBalance() >= ammounttransfer);
				if(allowable){
					if(srcBA.widthrawAmmount(ammounttransfer)){
						if( destBA.depositAmmount(ammounttransfer)) {
							this.setTransactionAsDone();
							return true;
						}else{
							srcBA.depositAmmount(ammounttransfer);//restore srcBA balance
							System.out.println("deposit of "+ammounttransfer+" failed");
							this.setTransactionAsFailed();
							return false;
						}
					}else{
						System.out.println("widthrawal of "+ammounttransfer+" failed");
						this.setTransactionAsFailed();
						return false;
					}
					
				}else{
					System.out.println("Not enough funds");
					this.setTransactionAsFailed();
					return false;
				}
				
				
				
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
			

		default:
			System.out.println("Unknown Transactiontype: "+ transactiontypeid);
			return false;
		}
	}
	
	public int setTransactionAsDone(){
		String sqlstatement = "UPDATE transactions SET transstatusid = ?, processedon = ? WHERE transactionid = ?";

		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setCharParam(1, Transaction.DONE);
		this.sqltable.setNowTimeStampParam(2);
		this.sqltable.setIntParam(3, transactionid);
		this.transactionstatusid = Transaction.DONE;
		return this.sqltable.executeUpdate();
	}
	
	public int setTransactionAsFailed(){
		String sqlstatement = "UPDATE transactions SET transstatusid = ?, processedon = ? WHERE transactionid = ?";

		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setCharParam(1, Transaction.FAILED);
		this.sqltable.setNowTimeStampParam(2);
		this.sqltable.setIntParam(3, transactionid);
		this.transactionstatusid = Transaction.FAILED;
		return this.sqltable.executeUpdate();
	}
	
	public int setTransactionAsExpired(){
		String sqlstatement = "UPDATE transactions SET transstatusid = ?, processedon = ? WHERE transactionid = ?";

		this.sqltable.setSQLStatement(sqlstatement);
		this.sqltable.setCharParam(1, Transaction.EXPIRED);
		this.sqltable.setNowTimeStampParam(2);
		this.sqltable.setIntParam(3, transactionid);
		this.transactionstatusid = Transaction.EXPIRED;
		return this.sqltable.executeUpdate();
	}
	
	/**
	 * Checks and updates transaction status based on expiry;
	 * @return true when expired,done or failed,- false when still pending
	 */
	public boolean checkAndUpdateIfExpired(){
		if(this.transactionstatusid == Transaction.PENDING){
			Timestamp now = SQLTable.getNowTimeStamp();
			if(now.compareTo(expiration) > 0){
				//expired
				this.setTransactionAsExpired();
				return true;
			}else{
				return false;
			}
			
		}else{
			return true;
		}
	}

	public String getTransactionOwner(){
		return PersonalAccount.getUsernameByPersonalAccountid(initiatedby);
	}
	/*
	 * ==================================STATIC METHODS ==========================================
	 */
	
	public static Message getPendingTransactions(int bankaccountid){
		System.out.println("Transaction - get pending: Fundstransfer transactions only");
		
		
		String sqlstatement = "SELECT transactionid,transactiontypeid,transstatusid,expiration,initiatedby, ammounttransfer " +
				"FROM transactions LEFT JOIN fundstransfers USING (transactionid) " +
				"WHERE transstatusid = ? AND bankaccountid = ?";
		SQLTable sqltable = new SQLTable(SQLConnection.getInstance().getConnection());
		
		sqltable.setSQLStatement(sqlstatement);
		sqltable.setCharParam(1, Transaction.PENDING);
		sqltable.setIntParam(2, bankaccountid);
		ResultSet rs = sqltable.executeSQL();
		
		Message list = new Message();
		int transNo;
		try {
			for( transNo = 0; rs.next(); transNo++){
				int transactionid =  rs.getInt(1);
				char transactiontypeid = rs.getString(2).toCharArray()[0];
				char transstatusid = rs.getString(3).toCharArray()[0];
				Timestamp expiry = rs.getTimestamp(4);
				int initiatedby = rs.getInt(5);
				double ammounttransfer = rs.getDouble(6);
				
				Timestamp now = sqltable.getNowTimeStamp();
				if(now.compareTo(expiry) > 0){
					//expired
					Transaction transaction = new Transaction(transactionid);
					transaction.setTransactionAsExpired();
					continue;
				}
				
				GregorianCalendar gcal = (GregorianCalendar) Calendar.getInstance();
				gcal.setTime(expiry);
				int expDay = gcal.get(Calendar.DAY_OF_MONTH);
				int expMonth = gcal.get(Calendar.MONTH);
				int expYear = gcal.get(Calendar.YEAR);
				
				String username = PersonalAccount.getUsernameByPersonalAccountid(initiatedby);
				int bankaccountno = BankAccount.getAccountnoByBankaccountid(bankaccountid);
				String transactiontype = Transaction.getTransactionTypeString(transactiontypeid);
				
				
				
				list.addAttribute("transaction" + transNo + "transactionid", transactionid);
				list.addAttribute("transaction" + transNo + "bankaccountno", bankaccountno);
				list.addAttribute("transaction" + transNo + "transactiontypeid", transactiontypeid);
				list.addAttribute("transaction" + transNo + "transactiontype", transactiontype);
				list.addAttribute("transaction" + transNo + "ammount", ammounttransfer);
				list.addAttribute("transaction" + transNo + "initiatedby", username);
				list.addAttribute("transaction" + transNo + "expiryDay", expDay);
				list.addAttribute("transaction" + transNo + "expiryMonth", expMonth);
				list.addAttribute("transaction" + transNo + "expiryYear", expYear);
			}
			list.addAttribute("transactioncount", transNo);
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return new Message();
		}
			
	}
	
	public static boolean ownsTransaction(int transactionid, int personalaccountid){
		Transaction transaction =  new Transaction(transactionid);
		int bankaccountid = transaction.bankaccountid;
		return BankAccount.verifyOwnership(personalaccountid, bankaccountid);
		
	}
	
	public static String getTransactionTypeString(char transactiontypeid){
		switch (transactiontypeid) {
		case FUNDS_TRANSFER:
			
			return "Transfer";
			
		}
		return null;
		
	}
	
	public static String getTransactionStatusString(char transstatusid){
		switch (transstatusid) {
		case PENDING:
			return "PENDING";
		case VALIDATED:
			return "VALIDATED";
		case EXPIRED:
			return "EXPIRED";
		case DONE:
			return "DONE";
			
		}
		return null;
		
	}
	
	public static Message getFundsTransferDetails(int transactionid){
		String sqlstatement = "SELECT ammounttransfer,srcaccountno,destaccountno,srcbank,destbank " +
				"FROM fundstransfers WHERE transactionid = ?;";
		
		SQLTable sqltable = new SQLTable(SQLConnection.getInstance().getConnection());

		sqltable.setSQLStatement(sqlstatement);
		sqltable.setIntParam(1, transactionid);
		ResultSet rs = sqltable.executeSQL();
		try {
			if( rs.first()) {
				Message list =  new Message();

				list.addAttribute("ammounttransfer", rs.getDouble(1));
				list.addAttribute("srcaccount", rs.getInt(2));
				list.addAttribute("destaccount", rs.getInt(3));
				list.addAttribute("srcbank", rs.getInt(4));
				list.addAttribute("destbank", rs.getInt(5));
				
				return list;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new Message();
		
	}
	
	/*=========================================================================================================================================
	 * ========================================================================================================================================
	 * ========================================================================================================================================
	 */

	
	
	/*
	public static void main(String[] args) throws SQLException, IOException {
		System.out.println("main-Transaction");
		
		SQLConnection sqlconn =  new SQLConnection();
		SQLTable sqltable = new SQLTable(sqlconn.getConnection());
		Transaction trans = new Transaction();
		trans.setSQLTable(sqltable);
		System.out.println("READY");
		
		Timestamp now = SQLTable.getNowTimeStamp(); // new Timestamp(new Date().getTime());
		
		GregorianCalendar gCalendar = new GregorianCalendar();
		//Jan 1, 2012 - 10:15:03pm
		gCalendar.set(2012, Calendar.JANUARY, 1, 22, 15, 3);
		
		
		Timestamp expiry = new Timestamp(gCalendar.getTimeInMillis());
		trans.setExpiration(expiry);
		
		gCalendar.set(2011, Calendar.DECEMBER, 1, 22, 15, 3);
		
		Timestamp processedon = new Timestamp(gCalendar.getTimeInMillis());
		trans.setProccessedOn(processedon);
		
		trans.setTransactionTypeID(Transaction.FUNDS_TRANSFER);
		trans.setTransactionStatusID(Transaction.PENDING);
		trans.setInitiatedBy(2);
		
		if(trans.insertToTransactionsTable()>0){
			System.out.println("yey");
		}else{
			System.out.println("FUUUU");
		}
	}
	*/
}
